import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class BusLineManager {
	public static final String BUS_LINE_TABLE = "BUS_LINE";
	public static final String LINE_STATION_TABLE = "LINE_STATION";
	public static final String LINE_ID_COLUMN = "line_id";
	public static final String NAME_COLUMN = "name";
	public static final String COMPANY_COLUMN = "company_id";
	public static final String DETAILS_COLUMN = "details";
	
	private static BusLineManager instance = null;
	public static BusLineManager getInstance() throws SQLException {
		if (instance == null) {
			instance = new BusLineManager();
			return instance;
		} else {
			return instance;
		}
	}
	protected BusLineManager() throws SQLException {
		conn = Database.getAccessDBConnection();
	}
	Connection conn = null;
	
	public BusLine getBusLine(int busLineId) {
		try {
			Statement stmt = conn.createStatement();
			stmt.execute("SELECT * FROM " + BUS_LINE_TABLE + " WHERE " + LINE_ID_COLUMN + "=" + busLineId);
			ResultSet rs = stmt.getResultSet();
			if (!rs.next()) {
				stmt.close();
				return null;
			} else {
				BusLine bl = new BusLine(rs);
				stmt.close();
				return bl;
			}
		} catch (SQLException e) {
			return null;
		}
	}
	
	public ArrayList<BusLine> getBusLineInZone(int zoneId) {
		ArrayList<BusLine> busLines = new ArrayList<BusLine>();
		try {
			StationManager sm = StationManager.getInstance();
			ArrayList<Station> stations = sm.getStationInZone(zoneId);
			for (Station station : stations) {
				Statement stmt = conn.createStatement();
/*				String sql = "SELECT * FROM " + BUS_LINE_TABLE + "," + LINE_STATION_TABLE 
							+ " WHERE LINE_STATION.line_id=" + BUS_LINE_TABLE + ".line_id"
							+ " AND " + LINE_STATION_TABLE + ".station_id = " + station.getId();*/
				String sql = "SELECT * FROM view_Line_LineStation WHERE station_id=" + station.getId();
				stmt.execute(sql);
				ResultSet rs = stmt.getResultSet();
				while (rs.next()) {
					BusLine bl = new BusLine(rs);
					boolean shouldAdd = true;
					for (BusLine tmp : busLines) {
						if (tmp.getId() == bl.getId()) {
							shouldAdd = false;
							break;
						}
					}
					if (shouldAdd) {
						busLines.add(bl);
					}
				}
				stmt.close();
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		return busLines;
	}
	
	public ArrayList<BusLine> getBusLineOverStation(int stationId) {
		ArrayList<BusLine> busLines = new ArrayList<BusLine>();
		try {
			Statement stmt = conn.createStatement();
			String sql = "SELECT * FROM " + LINE_STATION_TABLE
					+ " WHERE " + "station_id = " + stationId;
			stmt.execute(sql);
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				int busLineId = rs.getInt("line_id");
				BusLine bl = getBusLine(busLineId);
				busLines.add(bl);
			}
			stmt.close();
		} catch (SQLException e) {
		}
		return busLines;
	}
	
	public ArrayList<BusLine> getAllBusLine() {
		ArrayList<BusLine> busLines = new ArrayList<BusLine>();
		try {
			Statement stmt = conn.createStatement();
			boolean a = stmt.execute("SELECT * FROM " + BUS_LINE_TABLE);
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				BusLine bl = new BusLine(rs);
				busLines.add(bl);
			}
			stmt.close();
		} catch (Exception e) {
		}
		return busLines;
	}
	
	public boolean addBusLine(BusLine busLine, ArrayList<Station> stations) {
		try {
			Statement stmt = conn.createStatement();
			String sql = "INSERT INTO " + BUS_LINE_TABLE + " (" + NAME_COLUMN + ")"
			+ " VALUES " + "('" + busLine.getName() + "')"; 
			stmt.executeUpdate(sql);
			stmt.execute("SELECT * FROM " + BUS_LINE_TABLE
					+ " WHERE " + NAME_COLUMN + "='" + busLine.getName() + "'");
			ResultSet rs = stmt.getResultSet();
			rs = stmt.getResultSet();
			rs.next();
			busLine.setId(rs.getInt("line_id"));
			for (int i = 0; i < stations.size(); i++) {
				stmt.executeUpdate("INSERT INTO " + LINE_STATION_TABLE
						+ " VALUES (" + busLine.getId() + "," + stations.get(i).getId() + "," + i + ")");
			}
/*			stmt.execute("SELECT * FROM " + BUS_LINE_TABLE
					+ " WHERE " + NAME_COLUMN + "='" + busLine.getName() + "'");
			ResultSet rs = stmt.getResultSet();
			if (rs.next()) {
				return false;
			} else {
				String sql = "INSERT INTO " + BUS_LINE_TABLE + " (" + NAME_COLUMN + ")"
						+ " VALUES " + "('" + busLine.getName() + "')"; 
				stmt.executeUpdate(sql);
				stmt.execute("SELECT * FROM " + BUS_LINE_TABLE
						+ " WHERE " + NAME_COLUMN + "='" + busLine.getName() + "'");
				rs = stmt.getResultSet();
				rs.next();
				busLine.setId(rs.getInt("line_id"));
				for (int i = 0; i < stations.size(); i++) {
					stmt.executeUpdate("INSERT INTO " + LINE_STATION_TABLE
							+ " VALUES (" + busLine.getId() + "," + stations.get(i).getId() + "," + i + ")");
				}
			}*/
			stmt.close();
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	
	public boolean updateBusLine(int busLineId, BusLine newBusLine, ArrayList<Station> newStations) {
		try {
			Statement stmt = conn.createStatement();
			stmt.execute("SELECT * FROM " + BUS_LINE_TABLE
					+ " WHERE " + NAME_COLUMN + "='" + newBusLine.getName() + "'");
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				int id = rs.getInt("line_id");
				if (busLineId != id) return false;
			}
				stmt.executeUpdate("UPDATE " + BUS_LINE_TABLE + " SET name='" + newBusLine.getName() + "' WHERE " + LINE_ID_COLUMN + "=" + busLineId);
				stmt.executeUpdate("DELETE FROM " + LINE_STATION_TABLE + " WHERE line_id=" + busLineId);
				for (int i = 0; i < newStations.size(); i++) {
					System.out.println("INSERT INTO " + LINE_STATION_TABLE
							+ " VALUES (" + busLineId + "," + newStations.get(i).getId() + "," + i + ")");
					stmt.executeUpdate("INSERT INTO " + LINE_STATION_TABLE
							+ " VALUES (" + busLineId + "," + newStations.get(i).getId() + "," + i + ")");
				}
			stmt.close();
			return true;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	public boolean removeBusLine(int id) {
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("DELETE FROM " + BUS_LINE_TABLE + " WHERE " + LINE_ID_COLUMN + "=" + id);
			stmt.executeUpdate("DELETE FROM " + LINE_STATION_TABLE + " WHERE " + LINE_ID_COLUMN + "=" + id);
			return true;
		} catch (Exception e) {
			return false;
		}
	}
}
